package com.example.myapplication;

import android.app.Activity;
import android.graphics.Color;
import android.os.Bundle;
import android.os.Handler;
import android.util.Log;

import java.sql.DriverManager;
import java.sql.ResultSet;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;

import com.github.mikephil.charting.data.Entry;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.Inflater;

public class DBUtils implements Runnable {
    private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://192.168.124.5:3306/db_library?useSSL=false&serverTimezone=UTC";
    private static final String DBUSER = "goodboy";
    private static final String DBPASSWORD = "1234";

    private Connection conn = null;
    private PreparedStatement stmt = null;
    private ResultSet rs = null;
    private Handler uiHandler = null;
    private View view = null;

    public DBUtils(){
    }
    public DBUtils(Handler h,View v){
        uiHandler = h;
        view = v;
    }

    /* 连接MySQL数据库 */
    public boolean LinkMysql() {
        try {
            Class.forName(DBDRIVER).newInstance();
            System.out.println("加载驱动成功");
        } catch (Exception e){
            System.out.println("加载驱动错误");
            e.printStackTrace();
            return false;
        }
        try{
            conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
            System.out.println("数据库连接成功");
            return true;
        } catch (Exception e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
            return false;
        }
    }

    /* 查询数据库，验证用户名与密码正确性 */
    public boolean LoginQuery(String username,String password) {
        try{
            String sql = "select * from user where username='"+username+"' and password='"+password+"';";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            if(rs!=null){
                rs.close();
                stmt.close();
                conn.close();
                return true;
            }
            rs.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /* 查询数据库 */
    public void TableQuery(String query, List<String> data,int flag) {
        try{
            String sql = "select * from record;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String date = rs.getString("date");
                String info = rs.getString("info");
                String temp = info.substring(0,7);
                String[] message = new String[4];
                if(info.length()>20&&(temp.equals("#RETURN")||temp.equals("#BORROW"))){
                    get_info_func(info,message);
                    if(flag==1){
                        if(message[1].equals(query)) {
                            if(temp.equals("#BORROW")){
                                data.add("\n时间："+date.substring(0,19)+"\n借阅人:"+message[1]+"\n书籍："+message[3]+"\n操作："+"借阅\n");
                            }else{
                                data.add("\n时间："+date.substring(0,19)+"\n借阅人:"+message[1]+"\n书籍："+message[3]+"\n操作："+"归还\n");
                            }
                        }
                    }else if(flag==2){
                        if(message[2].equals(query)) {
                            if(temp.equals("#BORROW")){
                                data.add("\n时间："+date.substring(0,19)+"\n借阅人:"+message[1]+"\n书籍："+message[3]+"\n操作："+"借阅\n");
                            }else{
                                data.add("\n时间："+date.substring(0,19)+"\n借阅人:"+message[1]+"\n书籍："+message[3]+"\n操作："+"归还\n");
                            }
                        }
                    }
                }
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /* 查询数据库 */
    public void ChartQuery(String query, int[]arr_borrow, int[] arr_return,int flag) {
        try{
            String sql = "select * from record;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String date = rs.getString("date");
                String info = rs.getString("info");
                String temp = info.substring(0,7);
                String[] message = new String[4];
                if(info.length()>20&&(temp.equals("#RETURN")||temp.equals("#BORROW"))){
                    get_info_func(info,message);
                    if(flag==1){
                        if(message[1].equals(query)) {
                            int month = Integer.parseInt(date.substring(5, 7));
                            if (temp.equals("#BORROW")) {
                                arr_borrow[month - 1]++;
                            } else {
                                arr_return[month - 1]++;
                            }
                        }
                    }else if(flag==2){
                        if(message[2].equals(query)) {
                            int month = Integer.parseInt(date.substring(5, 7));
                            if (temp.equals("#BORROW")) {
                                arr_borrow[month - 1]++;
                            } else {
                                arr_return[month - 1]++;
                            }
                        }
                    }
                }
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void run() {
        // 建立MySQL连接
        try {
            Class.forName(DBDRIVER).newInstance();
            System.out.println("加载驱动成功");
        } catch (Exception e){
            System.out.println("加载驱动错误");
            e.printStackTrace();
        }
        try{
            conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
            System.out.println("数据库连接成功");
        } catch (Exception e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }
        // 获取UI控件ID
        TextView text_s1_val = view.findViewById(R.id.text_s1_val);
        TextView text_s2_val = view.findViewById(R.id.text_s2_val);
        TextView text_flag1 = view.findViewById(R.id.text_flag1);
        TextView text_flag2 = view.findViewById(R.id.text_flag2);
        EditText edit_s1_th = view.findViewById(R.id.edit_s1_th);
        EditText edit_s2_th = view.findViewById(R.id.edit_s2_th);
        // 5秒循环获取当前环境值
        while (true){
            try{
                // 查询current_val表
                String sql = "select * from current_val;";
                stmt = conn.prepareStatement(sql);
                rs = stmt.executeQuery();

                while (rs.next()) {
                    String s1_val = rs.getString("s1_val");
                    String s2_val = rs.getString("s2_val");
                    String flag1 = rs.getString("flag1");
                    String flag2 = rs.getString("flag2");
                    Runnable runnable = new Runnable() {
                        @Override
                        public void run() {
                            text_s1_val.setText(s1_val+"%");
                            text_s2_val.setText(s2_val+"%");
                            if(flag1.equals("0")){
                                text_flag1.setText("开启");
                                text_flag1.setTextColor(Color.rgb(0,255,0));
                            }else{
                                text_flag1.setText("关闭");
                                text_flag1.setTextColor(Color.rgb(255,0,0));
                            }
                            if(flag2.equals("0")){
                                text_flag2.setText("安全");
                                text_flag2.setTextColor(Color.rgb(0,255,0));
                            }else{
                                text_flag2.setText("危险");
                                text_flag2.setTextColor(Color.rgb(255,0,0));
                            }
                        }
                    };
                    uiHandler.post(runnable);
                }
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            // 休眠5秒
            try {
                Thread.sleep(5000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }

    void get_info_func(String cmd,String[] s){
        int len = cmd.length();
        int[] temp = {0,0,0,0};
        int num = 0;
        for(int a=0;a<4;++a){
            temp[a] = cmd.indexOf('|',num);
            num = temp[a]+1;
        }
        for(int a=0;a<3;++a){
            s[a] = cmd.substring(temp[a]+1,temp[a+1]);
            System.out.println(s[a]);
        }
        s[3] = cmd.substring(temp[3]+1,len-1);
        System.out.println(s[3]);
    }
}

